package com.medical.dao;

import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import com.baomidou.mybatisplus.mapper.BaseMapper;
import com.medical.entity.CourseEvaluate;

public interface CourseEvaluateMapper extends BaseMapper<CourseEvaluate> {
    int deleteByPrimaryKey(Integer id);

    int insertSelective(CourseEvaluate record);

    CourseEvaluate selectByPrimaryKey(Integer id);

    int updateByPrimaryKeySelective(CourseEvaluate record);

    int updateByPrimaryKey(CourseEvaluate record);
    
    @Select("SELECT oe.*,u.nickname FROM t_course_evaluate as oe LEFT JOIN t_user as u ON oe.userid=u.id WHERE oe.courseorderid=#{courseId}")
    public Map<String, Object> selectByOrderId(@Param("courseId")int courseId);
    
    @Select("SELECT oe.content,oe.score,u.nickname,u.portrait FROM t_course_evaluate as oe LEFT JOIN t_user as u ON oe.userid=u.id LEFT JOIN t_course_order as te ON te.id=oe.courseid ${sql}")
    public List<Map<String, Object>> selectByTeacherid(@Param("sql")String sql);
    
    @Select("SELECT IFNULL(avg(oe.score),0.0) as fen FROM t_course_evaluate as oe LEFT JOIN t_course_order as te ON te.id=oe.courseid  WHERE te.courseid=#{courseId} and oe.coursetype=#{type}")
    public Double selectAvgScoreByeacherId(@Param("courseId")int courseId,@Param("type")int type);
    
    @Select("SELECT oe.content,oe.score,u.nickname,u.portrait FROM t_course_evaluate as oe LEFT JOIN t_user as u ON oe.userid=u.id LEFT JOIN t_course_seeing as te ON oe.courseid=te.id WHERE oe.coursetype=1 AND te.schoolid=#{schoolId} UNION ALL SELECT oe.content,oe.score,u.nickname,u.portrait FROM t_course_evaluate as oe LEFT JOIN t_user as u ON oe.userid=u.id LEFT JOIN t_course_streaming as te ON oe.courseid=te.id WHERE oe.coursetype=2 AND te.schoolid=#{schoolId} UNION ALL SELECT oe.content,oe.score,u.nickname,u.portrait FROM t_course_evaluate as oe LEFT JOIN t_user as u ON oe.userid=u.id LEFT JOIN t_course_offline as te ON oe.courseid=te.id WHERE oe.coursetype=3 AND te.schoolid=#{schoolId} ")
    public List<Map<String, Object>> selectBySchoolId(@Param("schoolId")int schoolId);
    
    @Select("SELECT IFNULL(AVG(score),0.0) FROM (SELECT IFNULL(SUM(score),0) as score FROM t_course_evaluate as ce LEFT JOIN t_course_seeing as cs ON ce.courseid=cs.id WHERE ce.coursetype=1 AND cs.schoolid=#{schoolId} UNION ALL SELECT IFNULL(SUM(score),0) as score FROM t_course_evaluate as ce LEFT JOIN t_course_streaming as cs ON ce.courseid=cs.id WHERE ce.coursetype=2 AND cs.schoolid=#{schoolId} UNION ALL SELECT IFNULL(SUM(score),0) as score FROM t_course_evaluate as ce LEFT JOIN t_course_offline as cs ON ce.courseid=cs.id WHERE ce.coursetype=3 AND cs.schoolid=#{schoolId}) as course")
    public Double selectAvgScoreBySchoolId(@Param("schoolId")int schoolId);
}